package com.supermap.dataservice.cloud.productC;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年12月04 下午 2:38
 * @email 1301584033@qq.com
 */
@Mapper
public interface ProductCloudMapper {

    /**
     * 指定获取该产品组下面的产品ff808081673e363101673fa3a93b0002
     * */
    @Select("select  t1.id as productId,t1.product_name as productName, date_format(t1.make_date,'%Y-%m-%d %H:%i:%S') as createTime ,t1.product_mainpic as image,t1.folder_name as folderName from (select id from t_b_productgroup where id='ff808081673e363101673fa3a93b0002') t2\n" +
            "inner join  t_b_pms_product t1\n" +
            "on t2.id= t1.product_group and t1.product_state = 6 \n" +
            "order by  t1.make_date desc " +
            "limit #{pageNum},#{pageSize}\n")
    List<Map<String,Object>> getGroupProduct(@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);

    /**
     * 指定获取该产品组下面的产品ff808081673e363101673fa3a93b0002
     * */
    @Select("select count(t1.id)  from (select id from t_b_productgroup where id='ff808081673e363101673fa3a93b0002') t2\n" +
            "inner join  t_b_pms_product t1\n" +
            "on t2.id= t1.product_group and t1.product_state = 6 \n" +
            "order by  t1.make_date desc" +
            "\n")
    int getGroupProductCount();

    @Select("select name from t_crop where code=#{cropId}")
    String getCropNameBuCropId(@Param("cropId")String cropId);

    /**
     * 获取公共服务产品统计数
     */
    @Select("\n" +
            "select count(t1.id) as countNum from t_b_pms_product t1\n" +
            "inner join (select id from t_b_productgroup where find_in_set(#{cropName},fit_biosname)) t2\n" +
            "on t1.product_group=t2.id and t1.product_state = 6\n")
    Integer getProductByCropName(@Param("cropName")String cropName);

    /**
     * 获取专家产统计数
     */
    @Select("select count(1) from t_expertproduct where cropsid in (${cropId}) ")
    Integer getExpertProductCounSum(@Param("cropId")String cropId);
    /**
     * */
    @Select("select group_concat(code) from t_crop where  parentid=#{cropId}")
    String getCropGroupConcat(@Param("cropId")String cropId);

    /**
     * 获取专家产品专家建议统计数
     */
    @Select("select count(id) as countNum from t_suggest where crop in (select code from t_crop where parentid=#{cropId}) \n")
    Integer getExpertSuggestBycrop(@Param("cropId")String cropId);

    /**
     * 获取重庆市作物农田总数
     */
    @Select("select t1.name as cropName ,count(t4.id) as countNum from t_crop t1\n" +
            "left join \n" +
            "(select t2.id,t3.crop from t_farmland t2\n" +
            "inner join t_farmcrops t3 on t2.id = t3.farmland\n" +
            " where t2.code like concat('50','%') and t2.del_flg = 0) t4\n" +
            " on t1.code = t4.crop\n" +
            " where t1.parentid = #{crop}\n" +
            " group by t1.code ")
    List<Map<String,Object>> getCqGroupAreaFarmCorpCountSum(@Param("crop") String crop);

    /**
     * 获取专家产品专家建议最多的N个专家排序
     */
   @SelectProvider(type = ProductCloudMapperSQL.class,method = "getExpertSuggestBycropCountSQL")
    List<Map<String,Object>> getExpertSuggestBycropCount(@Param("areaCode")String areaCode,@Param("crop")String crop,@Param("pageSize")Integer pageSize);

    class ProductCloudMapperSQL{
        public String getExpertSuggestBycropCountSQL(Map<String,Object> map){
            String areaCode=(String)map.get("areaCode");
            String crop=(String)map.get("crop");
            Integer pageSize=(Integer)map.get("pageSize");
            StringBuffer stringBuffer =new StringBuffer();
            if(areaCode==null) {
                stringBuffer.append("select * from \n" +
                        "            (select t1.showName,((count(distinct t5.id ) * 5) + if(t8.praNum is null,0,t8.praNum) +count(t9.id)) countNum from t_user t1 \n" +
                        "            inner join t_expertinfo t2 on t1.id = t2.userid\n" +
                        "            inner join t_expert_subject t3 on t2.id = t3.expertId\n" +
                        "            inner join t_subject_crops t4 on t3.subjectId = t4.subject \n" +
                        "            left join t_suggest t5 on t2.userid = t5.userid and t5.del_flg =0\n" +
                        "            left join \n" +
                        "            (select t6.userid,count(t7.userId)praNum from t_suggest t6 \n" +
                        "            inner join t_suggest_praise t7 on t6.id = t7.suggestId\n" +
                        "            group by t6.userid) t8  on t1.id = t8.userid\n" +
                        "            left join t_advinfocomment t9 on t1.id = t9.userId\n" +
                        "            where 1=1 ");


                if (crop != null) {
                    stringBuffer.append(" and t4.crop = " + crop + " ");
                }
                stringBuffer.append(" and t1.del_flg = 0 and t2.del_flg = 0 \n" +
                        "            group by t1.id\n" +
                        "            order by countNum desc\n" +
                        "            limit " + pageSize + ") t\n" +
                        "            order by t.countNum asc ");
                return stringBuffer.toString();
            }else{
                stringBuffer.append("select t1.id, t1.showName, ((count(distinct t5.id ) * 5) +  if(t8.praNum is null,0,t8.praNum) +count(t9.id)) countNum from t_user t1 \n" +
                        "inner join t_expertinfo t2 on t1.id = t2.userid\n" +
                        "left join t_suggest t5 on t2.userid = t5.userid and t5.del_flg =0\n" +
                        "left join \n" +
                        "(select t6.userid,count(t7.userId)praNum from t_suggest t6 \n" +
                        "inner join t_suggest_praise t7 on t6.id = t7.suggestId\n" +
                        "group by t6.userid) t8  on t1.id = t8.userid\n" +
                        "left join t_advinfocomment t9 on t1.id = t9.userId\n" +
                        "where t1.areaCode like concat("+areaCode+",'%')  and t1.del_flg = 0 and t2.del_flg = 0 \n" +
                        "group by t1.id\n" +
                        "order by countNum desc limit "+pageSize+"");
                return stringBuffer.toString();
            }
        }
    }
}
